#!/usr/local/bin/groovy
import java.sql.Connection
import java.sql.DriverManager
import java.sql.PreparedStatement
import java.util.concurrent.atomic.AtomicInteger

//10.108.6.15:1521/wfw.     JXPRECRUX1221/yygov1221
//数据库地址
def url = "jdbc:oracle:thin:@10.10.66.71:1521:orcl"
//用户名
def username = "dfdev_crux20191224"
//密码
def password = "crux"
Class.forName("oracle.jdbc.driver.OracleDriver")
def conn = DriverManager.getConnection(url, username, password)

/***
 * 步骤：
 * 1.查询用户是否有角色
 * 2.判断用户在角色下是否分配有组织
 * 3.把没有分配组织的用户设取用户的所属组织作为组织权限
 * 4.查询所有的用户有单位的权限，但是没有区划权限的，插入一个同级的区划权限
 */
conn.setAutoCommit(false)
//查询组织类型对应的权限类型ID
def psorg = conn.prepareStatement("select id from permission_per_type where code='Organization'")
def orgrs = psorg.executeQuery()
orgrs.next()
def typeId = orgrs.getLong("id")
println("组织权限类型id:${typeId}")
//查询用户有角色没有组织权限的用户集合
def ps = conn.prepareStatement("select u.ID as userId,u.ORGANIZATION as orgId,u.BELONG_TYPE as orgType,u.region as regionId from USERCENTER_USER u where u.ORGANIZATION is not null and u.ID in(     select ufg.USER_ID from permission_user_func_group ufg where ufg.user_id not in(select pa.SUBJECT_ID from permission_per_assignment pa where pa.type_id=:typeId))")
ps.setLong(1, typeId)
def rs = ps.executeQuery()
def ufgs = []
//向PA插入数据
def myPaPs = conn.prepareStatement("insert into permission_per_assignment(id,last_modified_version,type_id,resource_id,resource_name,can_access,subject_id,subject_type,permission_range,organization,function_group_id) values(:id,:last_modified_version,:type_id,:resource_id,:resource_name,:can_access,:subject_id,:subject_type,:permission_range,:organization,:function_group_id)")

while (rs.next()) {
    def ufg = {}
    ufg.userId = rs.getLong("userId");
    ufg.orgId = rs.getLong("orgId");
    ufg.orgType = rs.getString("orgType");
    ufg.regionId = rs.getLong("regionId")
    ufg.roles = []
    ufgs.add(ufg)
    println("用户${ufg.userId}在组织${ufg.orgId}类型${ufg.orgType}(用户所属区划:${ufg.regionId})没有组织权限")
    def ps2 = conn.prepareStatement("select function_group_id as fgId from permission_user_func_group where user_id=:userId")
    ps2.setLong(1, userId);
    def rs2 = ps2.executeQuery()
    while (rs2.next()) {
        def roleId = rs2.getLong("fgId");
        if (roleId != 0) {
            ufg.roles.add(roleId);
            println("用户:${ufg.userId}的角色:${roleId}")
        }
    }
    ps2.close()
    rs2.close();

    ufg.roles.forEach({ role ->
        println("在用户${ufg.userId}角色${role}下插入组织权限:${ufg.orgId}")
        def resourceId = "id in (${ufg.orgId})";
        def pa = new Pa(id: GenId.generate(), lastModifiedVersion: 0, typeId: typeId, resourceId: resourceId, resourceName: ufg.orgType, canAccess: 1, subjectId: ufg.userId, subjectType: "USER", permissionRange: "SAME_LEVEL", organization: ufg.orgId, functionGroupId: role);
        batchInsertIntoPa(myPaPs, pa)
    })
}


println("开始插入单位本级的区划")
insertIntoSameRegion(conn, typeId, myPaPs)
myPaPs.executeBatch()
println("开始插入单位本级的区划----完成")
conn.commit()
rs.close()
ps.close()
conn.close()


void insertIntoSameRegion(Connection connection, long orgTypeId, PreparedStatement preparedStatement) {
    def ps = connection.prepareStatement("select pa.organization as contextOrgId,pa.id as id,pa.subject_id as userId,pa.resource_id as rid,pa.function_group_id as roleId " +
            "from permission_per_assignment pa " +
            "where pa.type_id=:orgTypeId and pa.permission_range=:permissionRange")
    ps.setLong(1, orgTypeId);
    ps.setString(2, "SAME_LEVEL");
    def rs = ps.executeQuery()
    def userPas = new HashMap<Long, UserPa>()
    def userOrgPas = new HashMap<String, UserOrgPa>()
    while (rs.next()) {
        def paId = rs.getLong("id")
        def subjectId = rs.getLong("userId")
        def rid = rs.getString("rid")
        def roleId = rs.getLong("roleId");
        def contextOrgId = rs.getLong("contextOrgId");
        def key = contextOrgId + "-" + subjectId;
        if (!userOrgPas.containsKey(key)) {
            userOrgPas.put(key, new UserOrgPa(contextOrgId: contextOrgId, userId: subjectId, roleDatas: []))
        }

        UserOrgPa userOrgPa = userOrgPas.get(key)


        if (rid.contains("in")) {
            println("查询到用户${subjectId}在角色${roleId}本级的组织权限${rid}")
            def startIdx = rid.indexOf("(")
            def endIdx = rid.indexOf(")")
            def id = rid.substring(startIdx + 1, endIdx)
            boolean exsitRole = false
            for (def rd in userOrgPa.roleDatas) {
                if (rd.roleId == roleId) {
                    rd.orgIds.add(Long.parseLong(id))
                    exsitRole = true
                }
            }
            if (!exsitRole) {
                userOrgPa.roleDatas.add(new UserRaData(roleId: roleId, orgIds: [Long.parseLong(id)]))
            }
        }

    }
    ps.close()
    rs.close()
    println("有${userPas.size()}个用户需要处理")

    userOrgPas.forEach({ k, v ->
        v.roleDatas.forEach({ r ->
            def orgIds = r.getOrgIds();
            if (orgIds.size() == 1) {
                println("开始处理用户:${v.getUserId()}在角色:${r}的单位${orgIds}")
                def ps3 = connection.prepareStatement("select id,d_type,region_id from (select id,D_TYPE,region_id from ORG_REGION union select id,D_TYPE,region_id from ORG_BANK union select id,D_TYPE,region_id from ORG_UNIT) where ID=:orgId")
                long userPaOrgId = orgIds.get(0);
                ps3.setLong(1, userPaOrgId)
                def rs3 = ps3.executeQuery()
                if (rs3.next()) {
                    String dtype = rs3.getString("d_type")
                    String regionId = rs3.getLong("region_id")
                    if (!"region".equalsIgnoreCase(dtype)) {
                        println("用户${v.getUserId()}的单位${userPaOrgId}不是区划需要增加同级区划${regionId}的权限")
                        def pa = new Pa(id: GenId.generate(), lastModifiedVersion: 0, typeId: orgTypeId, resourceId: "id in (" + regionId + ")", resourceName: dtype, canAccess: 1, subjectId: v.getUserId(), subjectType: "USER", permissionRange: "SAME_LEVEL", organization: orgIds.get(0), functionGroupId: r.roleId);
                        batchInsertIntoPa(preparedStatement, pa)
                    } else {
                        println("用户${v.getUserId()}的单位${userPaOrgId}是区划不需要处理")
                    }
                }
                rs3.close()
                ps3.close()
            }
        })
    })

}

class UserPa {
    long id
    long userId
    def orgIds = new ArrayList<Long>();
    long roleId;
}

class Pa {
    long id
    long typeId
    long lastModifiedVersion = 0
    String resourceId
    String resourceName
    boolean canAccess = true
    long subjectId
    String subjectType
    String permissionRange = "SAME_LEVEL"
    long organization
    long functionGroupId
}

class UserOrgPa {
    long userId
    long contextOrgId
    List<UserRaData> roleDatas = []
}

class UserRaData {
    long roleId;
    List<Long> orgIds = [];
}


void batchInsertIntoPa(PreparedStatement paPs, Pa pa) {
    //id
    paPs.setLong(1, pa.getId())
    //last_modified_version
    paPs.setLong(2, 0);
    //type_id
    paPs.setLong(3, pa.getTypeId());
    //resource_id
    paPs.setString(4, pa.getResourceId());
    //resource_name
    paPs.setString(5, pa.getResourceName());
    //can_access
    paPs.setInt(6, 1);
    //subject_id
    paPs.setLong(7, pa.getSubjectId());
    //subject_type
    paPs.setString(8, "USER");
    //permission_range
    paPs.setString(9, "SAME_LEVEL");
    //organization
    paPs.setLong(10, pa.getOrganization());
    //function_group_id
    paPs.setLong(11, pa.getFunctionGroupId());
    paPs.addBatch()
}

class GenId {
    static AtomicInteger seq = new AtomicInteger(0);
    static long workId = (long) (new Random(System.nanoTime())).nextInt(2048);

    static long generate() {
        return System.currentTimeMillis() << 21 | workId << 10 | (long) seq.getAndUpdate({ operand ->
            ++operand;
            return operand % 1024;
        });
    }
}
